| |
99 Search Results Found |
| 1 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 15 September 2015 08:49 «»
By: CraigB
Re: Dynamic SQL in cursor
… use a Named Cursor with Native Dynamic SQL (NDS). And, since the goal of your function appears to be "Return the record set produced by your NDS query", you don't need to loop through the result set, you simply need to Execute you NDS …
|
| 2 |
Forum: Data Guard «»
Posted on: Wed, 24 December 2014 07:27 «»
By: sam524
Re: data guard configuration on windows
…0.2.0\admin\ora\udump'
############Added by NDS
DB_NAME=ora
DB_UNIQUE_NAME=ora
LOG_ARCHIVE_CONFIG='DG_CONFIG=(ora,stdby)'
LOG_ARCHIVE_DEST_1='LOCATION=c:\oracle\product\10.2.0\ARCHIVE VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ora'
…
|
| 3 |
Forum: Precompilers, OCI & OCCI «»
Posted on: Fri, 13 September 2013 06:50 «»
By: gupta0kumud
not all variable bound
…, "
" olpadmin.bins b , olpadmin.nds_bin_order g,"
"(SELECT SUBSTR(bin,1,2) bin_prefix, "
" MAX(SUBSTR(bin,3,2)) binmax FROM "
" olpadmin.bins WHERE owner = :customer.ordTypeStr "
" AND bin_grp = '…
|
| 4 |
Forum: SQL & PL/SQL «»
Posted on: Fri, 27 April 2012 10:09 «»
By: Michel Cadot
Re: Parameterised Cursor creation
…VARCHAR2(30);
13
14 -- Create NDS statement, with a bind or placeholder variable.
15 stmt VARCHAR2(2000) := 'SELECT CIS_CODE as CIS_CODE FROM '
16 || IN_TABLE_NAME ;
17
18 REC varchar2(100);
19…
|
| 5 |
Forum: SQL & PL/SQL «»
Posted on: Fri, 27 April 2012 09:29 «»
By: sulabhagra
Re: Parameterised Cursor creation
… item_type_out VARCHAR2(30);
-- Create NDS statement, with a bind or placeholder variable.
stmt VARCHAR2(2000) := 'SELECT CIS_CODE as CIS_CODE FROM '
|| IN_TABLE_NAME ;
REC varchar2(100);
BEGIN
…
|
| 6 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 12 May 2010 06:27 «»
By: JRowbottom
Re: To bind or not to bind .... that is the question.
…qualified approval to the idea of using NDS with literals instead of binds.
What I would suggest is that into every dynamic query you create like this, you add a comment so that it is possible to get from v$sql back to the code that executes the query…
|
| 7 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 11 May 2010 06:47 «»
By: JRowbottom
Re: To bind or not to bind .... that is the question.
…depends largely on whether your queries are infrequent and long(ish) running, or short and frequent.
If the query is infrequent, complex and long running, and getting a very good plan matters, then using literals is a good strategy - the parse time is …
|
| 8 |
Forum: SQL & PL/SQL «»
Posted on: Thu, 25 March 2010 03:10 «»
By: JRowbottom
Re: PL/SQL, Static SQL slower then Dynamic SQL
…@mnitu - your own results show that the NDS approach used more latches:Name Run1 Run2 Diff
LATCH.library cache pin alloca 2,906 22,965 20,059
LATCH.shared pool 14,358 …
|
| 9 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 24 March 2010 13:17 «»
By: mnitu
Re: PL/SQL, Static SQL slower then Dynamic SQL
…/SQL context.
But in the second part: the NDS one, execution starts with PL/SQL engine. The NDS hires SQL engine. This one discovers that it is non SQL but PL/SQL and hires back PL/SQL Engine. So this time there are some context changes: PL/SQL => …
|
| 10 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 24 March 2010 12:27 «»
By: JRowbottom
Re: PL/SQL, Static SQL slower then Dynamic SQL
…concerning a version of Oracle in which NDS most definitely was slower (about which more later)
2) a blog entry - if an entry in a blog is incorrect or inaccurate, I for one will not be terribly suprised
3) a paragraph from a white paper taken almost …
|
| 11 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 24 March 2010 10:48 «»
By: JRowbottom
Re: PL/SQL, Static SQL slower then Dynamic SQL
…of the difference as possible is from the NDS overhead.
declare
v_iter pls_integer := 1000000;
v_start_time pls_integer;
v_end_time pls_integer;
begin
v_start_time := to_number(to_char(systimestamp,'sssss.ff'))*1000;
for i in 1…
|
| 12 |
Forum: SQL & PL/SQL «»
Posted on: Mon, 16 November 2009 04:23 «»
By: pablolee
Re: Which is better - CHR(39) or '''' ?
…posts prior to your post) In addition, NDS simply would NOT be the preferred solution here if incorporating the task into PL/SQL. One would use exactly the same method and if supplying the text values at runtime, simple, local PL/SQL variables would do …
|
| 13 |
Forum: SQL & PL/SQL «»
Posted on: Mon, 16 November 2009 04:12 «»
By: JRowbottom
Re: Which is better - CHR(39) or '''' ?
I have to take issuse with this statement.
NDS may well be the preferred (or at least simplest) way of executing dynamic SQL, but the preferred way of doing it is to avoid dynamic SQL entirely.
|
| 14 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 08 September 2009 07:42 «»
By: JRowbottom
Re: Optimization in the where clause
…that this line: ' A.CLCUSTCUSTCD is null))' || argClientList || is a large part of the reason why they're using NDS.
Whether or not the need to depends entirely on the contents of that string, as the rest fo the query is fine for normal SQL…
|
| 15 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 02 September 2009 00:29 «»
By: bishtoo
Re: condition
…this new column already exists or not.
then, by using NDS we can alter and add new column to that table.
sqlstmt:= 'ALTER TABLE' || ' ' || tab1 || ' ' || 'ADD' || ' '|| col1 || ' ' || 'VARCHAR2(100) default null';
execute immediate sqlstmt…
|
| 16 |
Forum: SQL & PL/SQL «»
Posted on: Fri, 07 August 2009 04:08 «»
By: JRowbottom
Re: Inquiry on calling procedures within a procedure
…NDS was causing a hard parse every time a procedure was executed. Removing this reduced cpu usage, and also reduced the time taken for procedures to execute.
Here's a little test you can use to see the difference in times for normal as opposed to NDS …
|
| 17 |
Forum: SQL & PL/SQL «»
Posted on: Fri, 07 August 2009 03:10 «»
By: JRowbottom
Re: Inquiry on calling procedures within a procedure
…had a ssytem that worked by using NDS to call a procedure dynamically, and made a noticable performance improvement by switching to an IF-THEN-ELSE list.
What you can do is use a hybrid approach - have an explicit IF list for everything that you …
|
| 18 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 14 April 2009 04:04 «»
By: JRowbottom
Re: Modifications required in existing Procedure
…g, and there's no two ways about it.
Commands executed using EXECUTE IMMEDIATE do not execute in a different Session. They do not even execute as a different transaction in the current session.
They are quite definitely executed as part of the current…
|
| 19 |
Forum: SQL & PL/SQL «»
Posted on: Fri, 03 October 2008 13:36 «»
By: ehegagoka
Re: Confusing Error
…Hi, here's what I tried on non-NDS code.
SQL> desc cogviews_mv_variancereport
PROCEDURE cogviews_mv_variancereport
Argument Name Type In/Out Default…
|
| 20 |
Forum: SQL & PL/SQL «»
Posted on: Fri, 03 October 2008 12:38 «»
By: ehegagoka
Confusing Error
…number of arguments error. What could be parameter I'm lacking?
Also when I convert the sql to NDS, then put the USING clause I get a ORA-600 "internal error code, arguments: [%s],[%s],[%s], [%s], [%s]. Please advised on any solutions. Thanks…
|
| 21 |
Forum: Forms «»
Posted on: Thu, 28 August 2008 05:19 «»
By: manudu20
Icons are not showing on client pc in forms 10g application
…//59.165.13.168:8889/forms/frmservlet?config=nds
As, its running properly but icons are not showing in client pc while its showing on server correctly.
I have done changes like
1.In "registry.dat"
default.icons.iconpath= http://59.165.…
|
| 22 |
Forum: SQL & PL/SQL «»
Posted on: Mon, 30 June 2008 02:08 «»
By: gamba
Generic SELECT procedure.
…rameters, by the way).
I then tried using NDS (with concatination):
sql_str := 'SELECT * FROM Products WHERE 1=1';
IF p_Id IS NOT NULL THEN
sql_str := sql_str || ' AND ProductId = || p_Id;
END IF;
the problem with this approach is that it's …
|
| 23 |
Forum: SQL & PL/SQL «»
Posted on: Mon, 14 April 2008 09:10 «»
By: pablolee
Re: Execute immediate
… as to why you are performing this task with NDS at all but let's leave that can of worm shut for the moment. All you need to concern yourself with is the correct structure of your EXECUTE IMMEDIATE statement (Joys point about the pointless to_char …
|
| 24 |
Forum: SQL & PL/SQL «»
Posted on: Mon, 14 April 2008 05:00 «»
By: pablolee
Re: Execute immediate
… in putting the parameter directly into the string. You have obviously read up in NDS as your code is pretty close to your requirements. Take a look at the USING clause of the execute immediate statement, it should give you an idea. any problems, post …
|
| 25 |
Forum: Server Utilities «»
Posted on: Wed, 16 January 2008 00:56 «»
By: jitender.sadh
Re: getting error during importing
…error 942:
"CREATE TRIGGER "NDS"."AFETR_INSERT" AFTER INSERT ON NEWTON.FAC_VCH_M REFERE"
"NCING NEW AS NEW OLD AS OLD FOR EACH ROW "
"DECLARE"
" TEMP_DIV_CODE VARCHAR2(6);"
" …
|
| 26 |
Forum: Server Utilities «»
Posted on: Tue, 15 January 2008 04:45 «»
By: jitender.sadh
Re: getting error during importing
…unlimited , during tablespace creation.
SQL> CREATE TABLESPACE NDS DATAFILE 'D:\DATAFILES\NDS.DMP' SIZE 4600M
2 AUTOEXTEND ON NEXT 450M
3 MAXSIZE UNLIMITED
4 extent management local;
Tablespace created.
regards
jitender…
|
| 27 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 12 December 2007 01:35 «»
By: Maaher
Re: sql%rowcount not working as expected. 0 rowcount?
…string to the SQL engine and let the SQL engine return the result set. The SQL%ROWCOUNT you're printing is the one from the PL/SQL side. It has no idea what string you passed to the SQL engine via NDS.
My advice? Avoid dynamic SQL if you can.
MHE…
|
| 28 |
Forum: Server Utilities «»
Posted on: Tue, 11 December 2007 00:07 «»
By: jitender.sadh
Re: export/import
…
12 rows selected.
SQL> create user nds identified by nds;
User created.
SQL> grant dba to nds;
Grant succeeded.
SQL> create tablespace nds datafile 'nds.dbf' size 4500m;
Tablespace created.
SQL> alter user nds quota 0 …
|
| 29 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 21 November 2007 01:11 «»
By: jitender.sadh
Re: error during inserting(ORA-01403: no data found)
…one it run sucessfully.
SQL> conn nds/nds@ora
Connected.
SQL> INSERT INTO FAC_VCH_M(VCH_LOCN_CODE,VCH_VCH_#, VCH_VCH_TyPe,VCH_VCH_DATE, DB_AMT,
2 CR_AMT, VCH_ENTR_BY, VCH_ENTR_DATE)
3 VALUES( '400001','0700012', '…
|
| 30 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 07 November 2007 07:08 «»
By: pablolee
Re: bad bind variables while creating a stored procedure
If this is your actual procedure, and this is an actual requirement and not simply an academic exercise in practicing nds, the follow the advice of flyboy in the last sentence of his post. i.e. there is no need for this to be done in NDS.
|
| 31 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 27 March 2007 04:45 «»
By: orafaqer
Re: DBMS_SQL: Bind list of values with select statement?
… my_collection;
-- ...
functions get_binds return my_collection
begin
return g_bind_values;
end;
--...
procedure some_prc(...)
begin
-- dbms_sql routine with query " ... where x in (select /*+ cardinality…
|
| 32 |
Forum: SQL & PL/SQL «»
Posted on: Fri, 16 February 2007 09:48 «»
By: oraclenub2006
Re: NO ROWS SELECTED when using BIND VARIABLE
…point.
3. I am not sure how I ended up w/ NDS and honestly I'm still trying to digest the difference as I am learning as I go along. I frankly, have no preference. I just need to get this to work by whichever means.
4. point well taken and thnaks …
|
| 33 |
Forum: SQL & PL/SQL «»
Posted on: Fri, 09 February 2007 15:55 «»
By: Todd Barry
Re: Dynamic SQL and Loop question
…SQL call).
That would let you avoid the NDS within the loop itself since you could then use static SQL with no owner prefix.
Alternatively, you would need to use a dynamic cursor instead of a cursor FOR loop;
type rc is ref cursor;
v_rc rc…
|
| 34 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 30 January 2007 05:57 «»
By: michael_bialik
Re: Temporary Table - Insertion dynamically
…proc.
2. Why are you using DBMS_SQL : NDS ( Native Dynamic SQL is simplier and usually better).
However if you move CREATE TABLE statement out of proc - you won't have to use dynamic SQL at all.
3. Only session inserting the data may see/reference …
|
| 35 |
Forum: Performance Tuning «»
Posted on: Sat, 06 January 2007 05:50 «»
By: michael_bialik
Re: Question about parsing and Bind variables
…problem is caused by NDS (native dynamic sql) usage:
when your cursor is closed NDS releases it's handle from session-level cache table, so the next time it's executed Oracle performs a search in SGA (soft parse).
AFAIK the problem is solved in 10g.
…
|
| 36 |
Forum: SQL & PL/SQL «»
Posted on: Sat, 02 December 2006 07:25 «»
By: michael_bialik
Re: display total number of rows with function and criteria
Search for NDS - Native Dynamic SQL
|
| 37 |
Forum: SQL & PL/SQL «»
Posted on: Fri, 24 November 2006 03:07 «»
By: JRowbottom
Re: TO EXECUTE SPOOL
…be to move the file to the Server, open it using Utl_File, read it in one line at a time and execute it using NDS.
Perhaps if you could provide more detail about what exactly you're trying to do here, then we could have a more productive discussion…
|
| 38 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 22 November 2006 02:09 «»
By: JRowbottom
Re: how to code this in PLSQL(dynamic)
Ok, so build the column list, table list and where clause list in pl/Sql, join them together and execute the query using NDS.
What problem have you encountered?
|
| 39 |
Forum: SQL & PL/SQL «»
Posted on: Thu, 16 November 2006 02:03 «»
By: JRowbottom
Re: PLS-00201: identifier 'ORDERSTOREDATA' must be declared
…rderStoreData, but no variable of that name is defined in the dynamic SQL.
The code executed by the Execute Immediate execute in it's own scope, and everything not defined explicitly as part of the NDS or passed in as a bind variable will be out of scope…
|
| 40 |
Forum: Forms «»
Posted on: Wed, 18 October 2006 23:07 «»
By: coulieo
Re: Java EXCEPTION_ACCESS_VIOLATION
… C:\WINDOWS\system32\netware\NWWS2NDS.DLL
0x50D50000 - 0x50D97000 C:\WINDOWS\system32\NETWIN32.DLL
0x50D00000 - 0x50D15000 C:\WINDOWS\system32\CLNWIN32.DLL
0x50DF0000 - 0x50E10000 C:\WINDOWS\system32\LOCWIN32.DLL
0x50DB0000 - 0x50DD9000 C:\WINDOWS…
|
Blog
Search
Help
Members
Register
Login
Home